﻿Imports System.Data.OleDb
Imports DTO

Public Class HocSinhDAO

#Region "Phan chuc nang"

    'Lay bang Hoc Sinh
    Public Function LayBang() As DataTable
        Dim dt As New DataTable
        Dim cn As OleDbConnection

        'Tao chuoi ket noi, mo ket noi co so du lieu
        cn = DataProvider.ConnectDB()


        'Tao chuoi strSQL de thao tac co so du lieu
        Dim strSQL As String
        strSQL = "Select * from HOCSINH"

        'Thuc thi chuoi sql
        Dim da As OleDbDataAdapter
        da = New OleDbDataAdapter(strSQL, cn)
        da.Fill(dt)

        'tra ve gia tri table va dong ket noi
        cn.Close()
        Return dt

    End Function


    Public Function LayDanhSach() As List(Of HocSinhDTO)
        'Tao chuoi ket noi, mo ket noi co so du lieu
        Dim cn As OleDbConnection
        cn = DataProvider.ConnectDB()


        'Tao chuoi strSQL de thao tac co so du lieu
        Dim strSQL As String
        strSQL = "Select * from HOCSINH"

        'Thuc thi chuoi sql
        Dim cmd As New OleDbCommand(strSQL, cn)
        Dim reader As OleDbDataReader = cmd.ExecuteReader()

        Dim ds As New List(Of HocSinhDTO)
        While (reader.Read())
            Dim hsDTO As New HocSinhDTO()
            hsDTO.MaHocSinh = reader("MaHocSinh")
            hsDTO.HoTen = reader("HoTen")
            hsDTO.GioiTinh = reader("GioiTinh")
            hsDTO.NgaySinh = reader("NgaySinh")
            hsDTO.DiaChi = reader("DiaChi")
            hsDTO.Email = reader("Email")
            hsDTO.MaLop = reader("MaLop")
            ds.Add(hsDTO)
        End While

        'tra ve gia tri table va dong ket noi
        cn.Close()
        Return ds

    End Function


    'ham Them Hoc Sinh
    Public Function ThemHocSinh(ByVal hs As HocSinhDTO) As Integer

        'tao ket noi
        Dim cn As OleDbConnection
        cn = DataProvider.ConnectDB()


        'tao chuoi sql thao tac CSDL
        Dim strSQL As String
        strSQL = "insert into HocSinh(HoTen,GioiTinh,NgaySinh,DiaChi,Email,MaLop) values (?,?,?,?,?,?)"

        'thuc thi chuoi sql
        Dim cmd As New OleDbCommand(strSQL, cn)

        'cmd.Parameters.Add("@MaHocSinh", OleDbType.Integer)
        cmd.Parameters.Add("@HoTen", OleDbType.WChar)
        cmd.Parameters.Add("@GioiTinh", OleDbType.WChar)
        cmd.Parameters.Add("@NgaySinh", OleDbType.Date)
        cmd.Parameters.Add("@DiaChi", OleDbType.WChar)
        cmd.Parameters.Add("@Email", OleDbType.WChar)
        cmd.Parameters.Add("@MaLop", OleDbType.Integer)

        'cmd.Parameters("@MaHocSinh").Value = ""
        cmd.Parameters("@HoTen").Value = hs.HoTen
        cmd.Parameters("@GioiTinh").Value = hs.GioiTinh
        cmd.Parameters("@NgaySinh").Value = hs.NgaySinh.ToShortDateString
        cmd.Parameters("@DiaChi").Value = hs.DiaChi
        cmd.Parameters("@Email").Value = hs.Email
        cmd.Parameters("@MaLop").Value = hs.MaLop
        
        Dim kq As Integer
        kq = cmd.ExecuteNonQuery()
        cn.Close()
        Return kq

    End Function


    Public Function XoaHocSinh(ByVal hsDTO As HocSinhDTO) As Integer
        Dim conn As OleDbConnection = DataProvider.ConnectDB()
        Dim strSQL As String = "DELETE FROM HOCSINH WHERE MaHocSInh=" + hsDTO.MaHocSinh.ToString()
        Dim cmd As New OleDbCommand(strSQL, conn)
        Dim r As Integer = cmd.ExecuteNonQuery()
        conn.Close()
        Return r
    End Function


    Public Function CapNhatHocSinh(ByVal hsDTO As HocSinhDTO) As Integer
        Dim conn As OleDbConnection = DataProvider.ConnectDB()
        Dim strSQL As String = "UPDATE HOCSINH SET HoTen='" + hsDTO.HoTen + "',GioiTinh='" + hsDTO.GioiTinh + "',NgaySinh='" + hsDTO.NgaySinh.ToShortDateString + "',DiaChi='" + hsDTO.DiaChi + "',Email='" + hsDTO.Email + "',MaLop=" + hsDTO.MaLop.ToString() + "  WHERE MaHocSinh=" + hsDTO.MaHocSinh.ToString()
        Dim cmd As New OleDbCommand(strSQL, conn)
        Dim r As Integer = cmd.ExecuteNonQuery()
        conn.Close()
        Return r
    End Function


#Region "TÌM KIẾM CƠ BẢN"
    'Tìm ki?m co b?n: theo tên
    Public Function TimKiemTheoTen(ByVal tenhs As String) As List(Of HocSinhDTO)
        Dim conn As OleDbConnection = DataProvider.ConnectDB()
        Dim strSQL = "SELECT * FROM HOCSINH WHERE HoTen LIKE '%" + tenhs + "%'"
        Dim cmd As New OleDbCommand(strSQL, conn)
        Dim reader As OleDbDataReader = cmd.ExecuteReader

        Dim ds As New List(Of HocSinhDTO)
        While (reader.Read())
            Dim hsDTO As New HocSinhDTO
            hsDTO.MaHocSinh = reader("MaHocSinh")
            hsDTO.HoTen = reader("HoTen")
            hsDTO.GioiTinh = reader("GioiTinh")
            hsDTO.NgaySinh = DateTime.Parse(reader("NgaySinh"))
            hsDTO.DiaChi = reader("DiaChi")
            hsDTO.Email = reader("Email")
            hsDTO.MaLop = reader("MaLop")
            ds.Add(hsDTO)
        End While

        conn.Close()
        Return ds
    End Function
    'Tìm ki?m co b?n: theo d?a ch?
    Public Function TimKiemTheoDiaChi(ByVal diachi As String) As List(Of HocSinhDTO)
        Dim conn As OleDbConnection = DataProvider.ConnectDB()
        Dim strSQL = "SELECT * FROM HOCSINH WHERE DiaChi LIKE '%" + diachi + "%'"
        Dim cmd As New OleDbCommand(strSQL, conn)
        Dim reader As OleDbDataReader = cmd.ExecuteReader

        Dim ds As New List(Of HocSinhDTO)
        While (reader.Read())
            Dim hsDTO As New HocSinhDTO
            hsDTO.MaHocSinh = reader("MaHocSinh")
            hsDTO.HoTen = reader("HoTen")
            hsDTO.GioiTinh = reader("GioiTinh")
            hsDTO.NgaySinh = DateTime.Parse(reader("NgaySinh"))
            hsDTO.DiaChi = reader("DiaChi")
            hsDTO.Email = reader("Email")
            hsDTO.MaLop = reader("MaLop")
            ds.Add(hsDTO)
        End While

        conn.Close()
        Return ds
    End Function
    'Tìm ki?m co b?n: theo mã l?p
    Public Function TimKiemTheoLop(ByVal malop As Integer) As List(Of HocSinhDTO)
        Dim conn As OleDbConnection = DataProvider.ConnectDB()
        Dim strSQL = "SELECT * FROM HOCSINH WHERE MaLop=" + malop.ToString()
        Dim cmd As New OleDbCommand(strSQL, conn)
        Dim reader As OleDbDataReader = cmd.ExecuteReader

        Dim ds As New List(Of HocSinhDTO)
        While (reader.Read())
            Dim hsDTO As New HocSinhDTO
            hsDTO.MaHocSinh = reader("MaHocSinh")
            hsDTO.HoTen = reader("HoTen")
            hsDTO.GioiTinh = reader("GioiTinh")
            hsDTO.NgaySinh = DateTime.Parse(reader("NgaySinh"))
            hsDTO.DiaChi = reader("DiaChi")
            hsDTO.Email = reader("Email")
            hsDTO.MaLop = reader("MaLop")
            ds.Add(hsDTO)
        End While

        conn.Close()
        Return ds
    End Function
#End Region





    Public Function BuildQuery(ByVal hsCRT As HocSinhCRT, ByVal conn As OleDbConnection) As OleDbCommand
        Dim cmd As New OleDbCommand()

        Dim strDKTen = " 1=1 "
        Dim strDKGioiTinh = " 1=1 "
        Dim strDKNgaySinh = " 1=1 "
        Dim strDKDiaChi = " 1=1 "
        Dim strDKLop = " 1=1 "
        Dim strDKMon = " 1=1 "
        Dim strDKDiem15Phut = " 1=1 "
        Dim strDKDiem1Tiet = " 1=1 "
        Dim strDKDiemHK = " 1=1 "

        If (hsCRT.TenHS <> "") Then
            strDKTen = " HoTen LIKE ? "
            cmd.Parameters.Add("@HoTen", OleDbType.WChar)
            cmd.Parameters("@HoTen").Value = "%" + hsCRT.TenHS + "%"
        End If

        If (hsCRT.CheckGioiTinh) Then
            strDKGioiTinh = " GioiTinh = ? "
            cmd.Parameters.Add("@GioiTinh", OleDbType.WChar)
            cmd.Parameters("@GioiTinh").Value = hsCRT.GioiTinh
        End If

        If (hsCRT.CheckNgaySinh) Then
            strDKNgaySinh = " NgaySinh between ? and ? "
            cmd.Parameters.Add("@NgaySinhTu", OleDbType.Date)
            cmd.Parameters.Add("@NgaySinhDen", OleDbType.Date)
            cmd.Parameters("@NgaySinhTu").Value = hsCRT.NgaySinhTu
            cmd.Parameters("@NgaySinhDen").Value = hsCRT.NgaySinhDen
        End If

        If (hsCRT.CheckDiaChi) Then
            strDKDiaChi = " DiaChi LIKE ? "
            cmd.Parameters.Add("@DiaChi", OleDbType.WChar)
            cmd.Parameters("@DiaChi").Value = "%" + hsCRT.DiaChi + "%"
        End If

        If (hsCRT.CheckLop) Then
            strDKLop = " LOP.MaLop = ? "
            cmd.Parameters.Add("@MaLop", OleDbType.Integer)
            cmd.Parameters("@MaLop").Value = hsCRT.MaLop
        End If

        If (hsCRT.CheckMon) Then
            strDKMon = " MONHOC.MaMon = ? "
            cmd.Parameters.Add("@MaMon", OleDbType.Integer)
            cmd.Parameters("@MaMon").Value = hsCRT.MaMon
        End If

        If (hsCRT.CheckDiem15Phut) Then
            strDKDiem15Phut = " Diem15P between ? and ? "
            cmd.Parameters.Add("@Diem15PhutTu", OleDbType.Double)
            cmd.Parameters.Add("@Diem15PhutDen", OleDbType.Double)
            cmd.Parameters("@Diem15PhutTu").Value = hsCRT.Diem15PhutTu
            cmd.Parameters("@Diem15PhutDen").Value = hsCRT.Diem15PhutDen
        End If
        If (hsCRT.CheckDiem1Tiet) Then
            strDKDiem1Tiet = " Diem1T between ? and ? "
            cmd.Parameters.Add("@Diem1TietTu", OleDbType.Double)
            cmd.Parameters.Add("@Diem1TietDen", OleDbType.Double)
            cmd.Parameters("@Diem1TietTu").Value = hsCRT.Diem1TietTu
            cmd.Parameters("@Diem1TietDen").Value = hsCRT.Diem1TietDen
        End If
        If (hsCRT.CheckDiemHK) Then
            strDKDiemHK = " DiemHK between ? and ? "
            cmd.Parameters.Add("@DiemHKTu", OleDbType.Double)
            cmd.Parameters.Add("@DiemHKDen", OleDbType.Double)
            cmd.Parameters("@DiemHKTu").Value = hsCRT.DiemKHTu
            cmd.Parameters("@DiemHKDen").Value = hsCRT.DiemKHDen
        End If

        Dim strDKWhere As String = " WHERE "
        strDKWhere += strDKTen
        strDKWhere += " and " + strDKGioiTinh
        strDKWhere += " and " + strDKNgaySinh
        strDKWhere += " and " + strDKDiaChi
        strDKWhere += " and " + strDKLop
        strDKWhere += " and " + strDKMon
        strDKWhere += " and " + strDKDiem15Phut
        strDKWhere += " and " + strDKDiem1Tiet
        strDKWhere += " and " + strDKDiemHK

        Dim strSQL As String = "SELECT HOCSINH.MAHOCSINH,HOCSINH.HoTen,HOCSINH.GioiTinh,HOCSINH.NgaySinh,HOCSINH.DiaChi,HOCSINH.Email, LOP.TenLop FROM MONHOC INNER JOIN ((LOP INNER JOIN (DIEMMONHOC INNER JOIN CHITIETDIEM ON DIEMMONHOC.MaDiemMonHoc = CHITIETDIEM.MaDiemMonHoc) ON LOP.MaLop = DIEMMONHOC.MaLop) INNER JOIN HOCSINH ON LOP.MaLop = HOCSINH.MaLop) ON MONHOC.MaMon = DIEMMONHOC.MaMon "
        strSQL += strDKWhere
        strSQL += " Group by HOCSINH.MAHOCSINH,HOCSINH.HoTen,HOCSINH.GioiTinh,HOCSINH.NgaySinh,HOCSINH.DiaChi,HOCSINH.Email, LOP.TenLop "
        strSQL += " Order by HoTen "

        cmd.Connection = conn
        cmd.CommandText = strSQL

        Return cmd
    End Function

    Public Function TimKiemNangCao(ByVal hsCRT As HocSinhCRT) As DataTable
        Dim dt As New DataTable()
        Dim conn As OleDbConnection
        conn = DataProvider.ConnectDB()
        Dim cmd As OleDbCommand = BuildQuery(hsCRT, conn)
        Dim da As New OleDbDataAdapter(cmd)
        da.Fill(dt)
        Return dt
    End Function


    Public Function LayDanhSachHocSinhTheoLop(ByVal malop As Integer) As List(Of HocSinhDTO)
        Dim ds As New List(Of HocSinhDTO)
        Dim conn As OleDbConnection
        Dim strSQL As String

        'Tao chuoi ket noi, mo ket noi co so du lieu
        conn = DataProvider.ConnectDB()

        'Tao chuoi strSQL de thao tac co so du lieu
        strSQL = "Select * From HOCSINH Where MaLop=" + malop.ToString()

        'Thuc thi chuoi sql và gán vào danh sách
        Dim cmd As New OleDbCommand(strSQL, conn)
        Dim reader As OleDbDataReader = cmd.ExecuteReader

        While (reader.Read())
            Dim hsDTO As New HocSinhDTO
            hsDTO.MaHocSinh = reader("MaHocSinh")
            hsDTO.HoTen = reader("HoTen")
            hsDTO.GioiTinh = reader("GioiTinh")
            hsDTO.NgaySinh = reader("NgaySinh")
            hsDTO.DiaChi = reader("DiaChi")
            hsDTO.Email = reader("Email")
            ds.Add(hsDTO)
        End While

        'tra ve gia tri List va dong ket noi
        conn.Close()
        Return ds
    End Function
#End Region




End Class
